package com.pxl.springbootinit.mapper;

import com.pxl.springbootinit.model.entity.Task;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author MADAO
* @description 针对表【task(任务发布表)】的数据库操作Mapper
* @createDate 2024-12-20 11:59:03
* @Entity com.pxl.springbootinit.model.entity.Task
*/
public interface TaskMapper extends BaseMapper<Task> {

    @Select("SELECT \n" +
            "    COALESCE(SUM(CASE WHEN DATE(t.created_time) = DATE(NOW() - INTERVAL 6 DAY) + INTERVAL a.a DAY THEN 1 ELSE 0 END), 0) AS new_tasks_count\n" +
            "FROM \n" +
            "    (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) AS a\n" +
            "LEFT JOIN `task` t \n" +
            "    ON DATE(t.created_time) = DATE(NOW() - INTERVAL 6 DAY) + INTERVAL a.a DAY\n" +
            "    AND t.is_deleted = 0\n" +
            "GROUP BY a.a;\n")
    public List<Integer> getRecent7DayAddCount();
}




